﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;

namespace Tools.MAME
{
    internal static class OnePerHelper
    {
        private class Record
        {
            public String GameName { get; private set; }
            public bool IsParent { get; private set; }
            public String Region { get; private set; }

            public Record(String gameName, bool isParent, String region)
            {
                GameName = gameName;
                IsParent = isParent;
                Region = region;
            }
        }

        private const int GAME_NAME_COL_INDEX = 0;
        private const int IS_PARENT_COL_INDEX = 1;
        private const int GROUP_COL_INDEX = 2;
        private const int REGION_COL_INDEX = 3;

        private const String TEMP_REGION_TABLE = "region";

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Process an intelligent 'one-per' game group filter, using a region as a hint to determine which regions to prioritize. </summary>
        /// <exception cref="ArgumentNullException"> Thrown when one or more required arguments are null. </exception>
        /// <exception cref="ArgumentException">     Thrown when one or more arguments have unsupported or
        /// illegal values. </exception>
        /// <param name="connection"> The connection. </param>
        /// <param name="filter">     Specifies the filter. </param>
        /// <returns> Temporary table where results reside. </returns>
        ///-------------------------------------------------------------------------------------------------
        public static String Process(SQLiteConnection connection, DatabaseFilter filter)
        {
            if (connection == null)
                throw new ArgumentNullException("connection");

            if (filter == null)
                throw new ArgumentNullException("filter");

            if (filter.Regions.Count != 1)
                throw new ArgumentException("Single region expected.");

            String[] regionPriorities = GetRegionPriority(filter.Regions.ToArray()[0]);

            using (SQLiteTransaction transaction = connection.BeginTransaction())
            {
                try
                {
                    // Drop existing temp table.
                    using (SQLiteCommand dropCommand = new SQLiteCommand(String.Format("drop table if exists [{0}]", TEMP_REGION_TABLE), connection, transaction))
                        dropCommand.ExecuteNonQuery();

                    String createQuery = String.Format("create temp table [{0}] ([{1}] text not null collate nocase)",
                        TEMP_REGION_TABLE, SqlConst.GAME_NAME_COL);

                    // Create new temp table.
                    using (SQLiteCommand createCommand = new SQLiteCommand(createQuery, connection, transaction))
                        createCommand.ExecuteNonQuery();

                    // Generate command used for inserting into temp table.
                    using (SQLiteCommand insertCommand = new SQLiteCommand(String.Format("insert into [{0}] values (?)", TEMP_REGION_TABLE), connection, transaction))
                    {
                        insertCommand.Parameters.Add(new SQLiteParameter());
                        insertCommand.Prepare();

                        StringBuilder sb = new StringBuilder();
                        sb.AppendFormat("SELECT [{0}], (case when [{1}] is null then 1 else 0 end), (case when [{1}] is null then [{0}] else [{1}] end) a, [{2}] from [{3}]",
                            SqlConst.GAME_NAME_COL, SqlConst.CLONE_OF_COL, SqlConst.REGION_COL, SqlConst.GAME_TABLE);

                        String whereClause = filter.BasicSQL;
                        if (!String.IsNullOrWhiteSpace(whereClause))
                            sb.AppendFormat(" where {0}", whereClause);

                        sb.AppendFormat(" order by a");

                        // Run select, grouping parents and clones together.
                        using (SQLiteCommand selectCommand = new SQLiteCommand(sb.ToString(), connection))
                        {
                            using (SQLiteDataReader reader = selectCommand.ExecuteReader())
                            {
                                if (reader.Read())
                                {
                                    // Read first group.
                                    String activeGroup = reader.GetString(GROUP_COL_INDEX);
                                    List<Record> currentRecords = new List<Record>();
                                    currentRecords.Add(new Record(reader.GetString(GAME_NAME_COL_INDEX), reader.GetBoolean(IS_PARENT_COL_INDEX), reader.GetString(REGION_COL_INDEX)));

                                    while (reader.Read())
                                    {
                                        String rowGroup = reader.GetString(GROUP_COL_INDEX);
                                        if (!rowGroup.Equals(activeGroup, StringComparison.OrdinalIgnoreCase))
                                        {
                                            //if (activeGroup.Equals("xmvsf", StringComparison.OrdinalIgnoreCase))
                                            //{
                                                // Found next group. Process current group.
                                                if (currentRecords.Count == 1)
                                                {
                                                    // Only one game, nothing else to check.
                                                    insertCommand.Parameters[0].Value = currentRecords[0].GameName;
                                                }
                                                else
                                                {
                                                    // Multiple games found.
                                                    foreach (String region in regionPriorities)
                                                    {
                                                        // Find all records that match this region.
                                                        List<int> matchingRecords = new List<int>();
                                                        for (int i = 0; i < currentRecords.Count; i++)
                                                            if (region.Equals(currentRecords[i].Region, StringComparison.OrdinalIgnoreCase))
                                                                matchingRecords.Add(i);

                                                        if (matchingRecords.Count > 0)
                                                        {
                                                            // Match was found, prioritize on parent.
                                                            bool parentFound = false;
                                                            foreach (int index in matchingRecords)
                                                            {
                                                                if (currentRecords[index].IsParent)
                                                                {
                                                                    insertCommand.Parameters[0].Value = currentRecords[index].GameName;
                                                                    parentFound = true;
                                                                    break;
                                                                }
                                                            }

                                                            if (!parentFound)
                                                            {
                                                                // No parent found, so just default to first matching record.
                                                                insertCommand.Parameters[0].Value = currentRecords[matchingRecords[0]].GameName;
                                                            }

                                                            break;
                                                        }
                                                    } // End outer (region) for each loop.                                        
                                                }

                                                // Insert single game from group.
                                                insertCommand.ExecuteNonQuery();
                                            //} // !!!!!!!!!!!!!!!!!!!!

                                            // Set new active group.
                                            activeGroup = rowGroup;

                                            // Clear records from last group.
                                            currentRecords.Clear();
                                        } // End group check.

                                        // Read record.
                                        currentRecords.Add(new Record(reader.GetString(GAME_NAME_COL_INDEX), reader.GetBoolean(IS_PARENT_COL_INDEX), reader.GetString(REGION_COL_INDEX)));
                                    } // End while loop.
                                } // End initial read.
                            } // End using reader.
                        } // End using select command.
                    } // End using insert command.

                    transaction.Commit();
                }
                catch
                {
                    try
                    {
                        transaction.Rollback();
                    }
                    catch
                    { }

                    throw;
                }
            } // End using transation.

            return TEMP_REGION_TABLE;
        }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Gets region priority list, based on supplied region. </summary>
        /// <exception cref="NotImplementedException"> Thrown when the requested operation is
        /// unimplemented. </exception>
        /// <param name="regionHint"> The region hint. </param>
        /// <returns> An array of string. </returns>
        ///-------------------------------------------------------------------------------------------------
        private static string[] GetRegionPriority(String regionHint)
        {
            if (regionHint.Equals("usa", StringComparison.OrdinalIgnoreCase))
            {
                return new String[] { "usa", "world", "unknown", "uk", "euro", "spain", "hispanic" ,"brazil", "france", 
                    "germany", "italy", "japan", "asia", "china", "hong kong", "taiwan", "korea", "australia", "austria", 
                    "hungary", "russia", "new zealand", "n/a"};
            }
            else
                throw new NotImplementedException();
        }
    }
}
